'use strict';

module.exports = {
  up: function (queryInterface, Sequelize) {
    //table of users
    queryInterface.createTable('users',{
      id: {
        type: Sequelize.BIGINT,
        primaryKey: true,
        autoIncrement: true
      },
      username: {
        type: Sequelize.STRING(50),
        allowNull: false,
        unique: true
      },
      name: {
        type: Sequelize.STRING(30)
      },
      phone: {
        type: Sequelize.STRING(20)
      },
      email: {
        type: Sequelize.STRING(50)
      },
      role: {
        type: Sequelize.STRING(20)
      },
      is_active_email: {
        type: Sequelize.BOOLEAN,
        allowNull: false,
        defaultValue: false
      },
      is_active_phone: {
        type: Sequelize.BOOLEAN,
        allowNull: false,
        defaultValue: false
      },
      created_at: {
        type: Sequelize.DATE,
        allowNull: false
      },
      updated_at: {
        type: Sequelize.DATE,
        allowNull: false
      },
      deleted_at: {
        type: Sequelize.DATE
      }
    });
    queryInterface.addIndex('users', ['phone']);
    queryInterface.addIndex('users', ['email']);
    //table of apartment business info
    queryInterface.createTable('am_business',{
      id: {
        type: Sequelize.BIGINT,
        primaryKey: true,
        autoIncrement: true
      },
      name: {
        type: Sequelize.STRING(50),
        allowNull: false
      },
      address: {
        type: Sequelize.STRING(100)
      },
      contact: {
        type: Sequelize.STRING(20)
      },
      am_user_id: {
        type: Sequelize.BIGINT,
        allowNull: false,
        unique: true
      },
      updated_at: {
        type: Sequelize.DATE,
        allowNull: false
      }
    });
    //table of apartment or area
    queryInterface.createTable('areas',{
      id: {
        type: Sequelize.BIGINT,
        primaryKey: true,
        autoIncrement: true
      },
      am_user_id: {
        type: Sequelize.BIGINT,
        allowNull: false,
        unique: false
      },
      area: {
        type: Sequelize.STRING(50),
        allowNull: false
      },
      address: {
        type: Sequelize.STRING(100)
      },
      ap_type: {
        type: Sequelize.STRING(20),
        allowNull: false
      },
      created_at: {
        type: Sequelize.DATE,
        allowNull: false
      },
      updated_at: {
        type:Sequelize.DATE,
        allowNull: false
      }
    });
    queryInterface.addIndex('areas',['area']);
    queryInterface.addIndex('areas',['area','address']);
    //table of room
    queryInterface.createTable('rooms',{
      id: {
        type: Sequelize.BIGINT,
        primaryKey: true,
        autoIncrement: true
      },
      building: {
        type: Sequelize.STRING(20)
      },
      number: {
        type: Sequelize.STRING(20),
        allowNull: false
      },
      area_id: {
        type: Sequelize.BIGINT,
        allowNull: false
      },
      remark: {
        type: Sequelize.STRING(50)
      },
      created_at: {
        type: Sequelize.DATE,
        allowNull: false
      },
      updated_at: {
        type: Sequelize.DATE,
        allowNull: false
      }
    });
    queryInterface.addIndex('rooms',['area_id']);
    queryInterface.addIndex('rooms',['building','number']);
    //table of doors
    queryInterface.createTable('doors',{
      id: {
        type: Sequelize.BIGINT,
        primaryKey: true,
        autoIncrement: true
      },
      number: {
        type: Sequelize.STRING(20),
        allowNull: false
      },
      remark: {
        type: Sequelize.STRING(50),
      },
      is_out: {
        type: Sequelize.BOOLEAN,
        allowNull: false
      },
      room_id: {
        type: Sequelize.BIGINT,
        allowNull: false
      },
      lock_dev_id: {
        type: Sequelize.STRING(100),
        unique: true
      },
      created_at: {
        type: Sequelize.DATE,
          allowNull: false
      },
      updated_at: {
        type: Sequelize.DATE,
          allowNull: false
      }
    });
    queryInterface.addIndex('doors',['room_id']);
    queryInterface.addIndex('doors',['number']);
    //table of tenant contract
    queryInterface.createTable('tenant_contracts',{
      id: {
        type: Sequelize.BIGINT,
        primaryKey: true,
        autoIncrement: true
      },
      ta_user_id: {
        type: Sequelize.BIGINT,
        allowNull: false
      },
      am_user_id: {
        type: Sequelize.BIGINT,
        allowNull: false
      },
      start_date: {
        type: Sequelize.DATE,
        allowNull: false
      },
      end_date: {
        type: Sequelize.DATE,
        allowNull: false
      },
      rent_price: {
        type: Sequelize.DECIMAL(11,2)
      },
      deposit: {
        type: Sequelize.DECIMAL(11,2)
      },
      deposit_refund: {
        type: Sequelize.DECIMAL(11,2)
      },
      created_at: {
        type: Sequelize.DATE,
        allowNull: false
      },
      updated_at: {
        type: Sequelize.DATE,
        allowNull: false
      }
    });
    queryInterface.addIndex('tenant_contracts',['ta_user_id']);
    queryInterface.addIndex('tenant_contracts',['am_user_id']);
    queryInterface.addIndex('tenant_contracts',['start_date','end_date']);
    queryInterface.createTable('room_contracts',{
      contract_id: {
        type: Sequelize.BIGINT,
        primaryKey: true
      },
      source_id: {
        type: Sequelize.BIGINT,
        primaryKey: true
      },
      source_type: {
        type: Sequelize.STRING(20),
        allowNull: false
      }
    });

    //table of door open authorities
    queryInterface.createTable('door_authorities',{
      id: {
        type: Sequelize.BIGINT,
        primaryKey: true,
        autoIncrement: true
      },
      auth_id: {
        type: Sequelize.INTEGER,
        allowNull: false
      },
      type: {
        type: Sequelize.STRING(20),
        allowNull: false,
        defaultValue: 'PLAIN'
      },
      password: {
        type: Sequelize.STRING(50),
        allowNull: false
      },
      start_time: {
        type: Sequelize.DATE,
        allowNull: false
      },
      end_time:{
        type: Sequelize.DATE,
        allowNull: false
      },
      created_user_id:{
        type: Sequelize.BIGINT,
        allowNull: false
      },
      username: {
        type: Sequelize.STRING(50)
      },
      door_id: {
        type: Sequelize.BIGINT
      },
      lock_dev_id:{
        type: Sequelize.STRING(100),
        allowNull: false
      },
      created_at: {
        type: Sequelize.DATE,
        allowNull: false
      },
      updated_at: {
        type: Sequelize.DATE,
          allowNull: false
      }
    });
    queryInterface.addIndex('door_authorities', ['door_id','auth_id']);
    queryInterface.addIndex('door_authorities', ['lock_dev_id','auth_id']);

    //table of gateway's devices
    queryInterface.createTable('area_gateways', {
      id: {
        type: Sequelize.BIGINT,
        primaryKey: true,
        autoIncrement: true
      },
      gateway_dev_id: {
        type: Sequelize.STRING(100),
        allowNull: false,
        unique: true
      },
      area_id: {
        type: Sequelize.BIGINT,
        allowNull: false
      },
      am_user_id:{
        type: Sequelize.BIGINT,
        allowNull: false
      }
    });
    queryInterface.addIndex('area_gateways',['area_id']);
    //table of system user operate logs
    queryInterface.createTable('sys_action_logs',{
      id: {
        type: Sequelize.BIGINT,
        primaryKey: true,
        autoIncrement: true
      },
      type: {
        type: Sequelize.STRING(50),
        allowNull: false
      },
      log_time: {
        type: Sequelize.DATE,
        allowNull: false
      },
      content: {
        type: Sequelize.TEXT
      }
    });
  },

  down: function (queryInterface, Sequelize) {

    return queryInterface.dropAllTables();
  }
};
